package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

/**
 * 用户信息 —SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwUserinfo {

    /**
     * 单个——获取单个用户信息
     */
    public JSONObject getUserOne(String user_encryptId,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  user_encryptId  AS user_encryptId,\n" +
                "  user_name       AS username,\n" +
                "  user_sex        AS sex,\n" +
                "  user_tel        AS tel,\n" +
                "  user_realName   AS realName,\n" +
                "  user_mail       AS user_mail,\n" +
                "  user_createtime AS createtime,\n" +
                "\n" +
                "  user_hobby_zt   AS user_hobby_zt,\n" +
                "  user_hobby_yw   AS user_hobby_yw\n" +
                "\n" +
                "FROM xw_userinfo \n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        //用户加密id
        if(!user_encryptId.equals("")){
            sb.append(" and user_encryptId in('" + user_encryptId + "') ");
        }


        try {

            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            result.put("status", 0);
            result.put("msg", "ok");

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("user_encryptId", (rs.getString("user_encryptId") == null ? "" : rs.getString("user_encryptId")));
                object.put("username", (rs.getString("username") == null ? "" : rs.getString("username")));
                object.put("sex", (rs.getString("sex") == null ? "" : rs.getString("sex")));
                object.put("tel", (rs.getString("tel") == null ? "" : rs.getString("tel")));
                object.put("realName", (rs.getString("realName") == null ? "" : rs.getString("realName")));
                object.put("user_mail", (rs.getString("user_mail") == null ? "" : rs.getString("user_mail")));
                object.put("user_hobby_zt", (rs.getString("user_hobby_zt") == null ? "" : rs.getString("user_hobby_zt")));
                object.put("user_hobby_yw", (rs.getString("user_hobby_yw") == null ? "" : rs.getString("user_hobby_yw")));
                object.put("createtime", (rs.getString("createtime") == null ? "" : DateUtil.DateToString(rs.getDate("createtime"))));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }


    /**
     * 单个——获取单个用户信息
     */
    public JSONObject getuidBusinessType(String user_encryptId,String user_role,String userid,
                                         JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.user_encryptId AS user_encryptId,\n" +
                "  a.user_name      AS username,\n" +
                "  a.user_role      AS user_role,\n" +
                "  b.business_type\n" +
                "FROM xw_userinfo a\n" +
                "  LEFT JOIN xw_business_info b ON a.user_encryptId = b.business_encryptId\n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        //用户加密id
        if(!user_encryptId.equals("")){
            sb.append(" and a.user_encryptId in('" + user_encryptId + "') ");
        }
        //用户加密id
        if(!user_role.equals("")){
            sb.append(" and a.user_role in('" + user_role + "') ");
        }
        //用户加密id
        if(!userid.equals("")){
            sb.append(" and a.user_id in('" + userid + "') ");
        }

        try {

            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            result.put("status", 0);
            result.put("msg", "ok");

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("user_encryptId", (rs.getString("user_encryptId") == null ? "" : rs.getString("user_encryptId")));
                object.put("username", (rs.getString("username") == null ? "" : rs.getString("username")));
                object.put("user_role", (rs.getString("user_role") == null ? "" : rs.getString("user_role")));
                object.put("business_type", (rs.getString("business_type") == null ? "" : rs.getString("business_type")));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);

        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }


    /**
     * 验证哟用户名是否存在
     */
    public JSONObject checkUsername(String username,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  count(user_name) AS usernameNumber\n" +
                "FROM xw_userinfo a \n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        //用户名
        if(!username.equals("")){
            sb.append(" and a.user_name = '" + username + "' ");
        }

        try {
            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            result.put("msg", "用户名可注册！");
            result.put("status", 0);
            result.put("errcode", 0);
            while (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("count:"+count);
                if(count>0){
                    result.put("errcode", 40001);
                    result.put("msg", "用户名已被注册！");
                }
            }


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        System.out.println("checkUsername:"+result.toString());
        return result;
    }


    /**
     * 验证哟用户名是否存在
     */
    public JSONObject checkUsertel(String tel,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  count(user_tel) AS usernameNumber\n" +
                "FROM xw_userinfo a \n";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        //用户名
        if(!tel.equals("")){
            sb.append(" and a.user_tel = '" + tel + "' ");
        }

        try {
            sql = sql + " where " + sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);

            result.put("msg", "电话号码可注册！");
            result.put("status", 0);
            result.put("errcode", 0);
            while (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("count:"+count);
                if(count>0){
                    result.put("errcode", 40001);
                    result.put("msg", "电话号码已被注册！");
                }
            }


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        System.out.println("checkUsertel:"+result.toString());
        return result;
    }


}
